﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Automobile.service.Model;
using FGWine;

namespace Automobile.service.DAL
{
    public class BlitemDal
    {
        string str = "Data Source=.;Initial Catalog=qichefuwu;Integrated Security=True";
        SQLHelper helper = new SQLHelper();
        /// <summary>
        /// 显示库存盘点信息
        /// </summary>
        /// <returns></returns>
        public List<BlitemModel> Blitem()
        {
            string sql = "select Blitem_Id,Blitem.Product_Id,Blitem_Etc,WareHose_Name,WareHose.WareHose_Id,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product.Product_Name,Blitem_Type.Blitem_Type_Id,Blitem_Type.Blitem_Type_name from Blitem inner join WareHose on Blitem.WareHose_Id=WareHose.WareHose_Id  inner join Product on Blitem.Product_Id=Product.Product_Id inner join Blitem_Type on Blitem.Blitem_Type_Id=Blitem_Type.Blitem_Type_Id ";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, str);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<BlitemModel> list = new List<BlitemModel>();
            foreach (DataRow item in table.Rows)
            {
                BlitemModel b = new BlitemModel();
                b.Blitem_Id = Convert.ToInt32(item["Blitem_Id"]);
                b.Blitem_Etc = item["Blitem_Etc"].ToString();
                b.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);               
                b.Blitem_date = Convert.ToDateTime(item["Blitem_date"]);
                b.Blitem_num = Convert.ToInt32(item["Blitem_num"]);
                b.Blitem_Rownum = Convert.ToInt32(item["Blitem_Rownum"]);
                b.Blitem_Stock_num = Convert.ToInt32(item["Blitem_Stock_num"]);
                b.Product_Id = Convert.ToInt32(item["Product_Id"]);
                b.Blitem_Type_Id = Convert.ToInt32(item["Blitem_Type_Id"]);
                b.WareHose_Name = item["WareHose_Name"].ToString();
                b.Product_Name = item["Product_Name"].ToString();
                b.Blitem_Type_name = item["Blitem_Type_name"].ToString();
                list.Add(b);
            }
            return list;
        }
        /// <summary>
        /// 添加库存盘点信息
        /// </summary>
        /// <returns></returns>
        public int AddBlitem(BlitemModel bb)
        {
            string sql = "insert into Blitem(Blitem_Etc,WareHose_Id,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product_Id,Blitem_Type_Id) values('" + bb.Blitem_Etc + "','" + bb.WareHose_Id + "','" + bb.Blitem_date + "','" + bb.Blitem_num + "','" + bb.Blitem_Rownum + "','" + bb.Blitem_Stock_num + "','" + bb.Product_Id + "','" + bb.Blitem_Type_Id + "')";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            int i = comm.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        /// <summary>
        /// 删除库存盘点信息
        /// </summary>
        /// <returns></returns>
        public int DeleBlitem(int Blitem_Id)
        {
            string sql = "delete from Blitem where Blitem_Id=" + Blitem_Id;
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            int i = comm.ExecuteNonQuery();
            conn.Close();
            return i;
        }
        /// <summary>
        /// 取单个库存盘点信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetBlitem(string Blitem_Id)
        {
            string sql = "select Blitem_Id,Blitem_Etc,WareHose_Id,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product_Id,Blitem_Type_Id from Blitem where Blitem_Id=" + Blitem_Id;

            SqlConnection conn = new SqlConnection(str);
            conn.Open();//打开数据库

            SqlCommand comm = new SqlCommand(sql, conn);
            DataTable table = new DataTable();
            SqlDataAdapter ada = new SqlDataAdapter(comm);
            ada.Fill(table);
            conn.Close();
            return table;
        }

        /// <summary>
        /// 更新商品库存信息
        /// </summary>
        /// <param name="stu"></param>
        /// <returns></returns>
        public int UpdateBlitem(BlitemModel b)
        {
            string sql = "update Blitem set Blitem_Etc='"+b.Blitem_Etc+"',WareHose_Id='"+b.WareHose_Id +"',Blitem_date='"+b.Blitem_date+"',Blitem_num='"+b.Blitem_num+"',Blitem_Rownum='"+b.Blitem_Rownum+"',Blitem_Stock_num ='"+b.Blitem_Stock_num+"',Product_Id='"+b.Product_Id+"',Blitem_Type_Id='"+b.Blitem_Type_Id+"' where Blitem_Id='"+b.Blitem_Id+"'";
            SqlConnection conn = new SqlConnection(str);
            conn.Open();//打开数据库

            SqlCommand comm = new SqlCommand(sql, conn);
            //执行命令
            int count = comm.ExecuteNonQuery();
            conn.Close();

            return count;
        }
        /// <summary>
        /// 显示库存盘点类型信息
        /// </summary>
        /// <returns></returns>
        public List<Blitem_TypeModel> Blitem_type()
        {
            string sql = "select Blitem_Type_Id,Blitem_Type_name from Blitem_Type ";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, str);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<Blitem_TypeModel> list = new List<Blitem_TypeModel>();
            foreach (DataRow item in table.Rows)
            {
                Blitem_TypeModel b = new Blitem_TypeModel();               
                b.Blitem_Type_Id = Convert.ToInt32(item["Blitem_Type_Id"]);
                b.Blitem_Type_name = item["Blitem_Type_name"].ToString();
                list.Add(b);
            }
            return list;
        }

        /// <summary>
        /// 搜索显示库存盘点信息
        /// </summary>
        /// <returns></returns>
        public List<BlitemModel> BlitemSelect(string Blitem_Etc)
        {
            string sql = "select Blitem_Id,Blitem.Product_Id,Blitem_Etc,WareHose_Name,WareHose.WareHose_Id,Blitem_date,Blitem_num,Blitem_Rownum,Blitem_Stock_num,Product.Product_Name,Blitem_Type.Blitem_Type_Id,Blitem_Type.Blitem_Type_name from Blitem inner join WareHose on Blitem.WareHose_Id=WareHose.WareHose_Id  inner join Product on Blitem.Product_Id=Product.Product_Id inner join Blitem_Type on Blitem.Blitem_Type_Id=Blitem_Type.Blitem_Type_Id  where 1=1";
            if (!string.IsNullOrEmpty(Blitem_Etc))
            {
                sql += " and Blitem_Etc like '%" + Blitem_Etc + "%'";
            }          

            DataTable table = helper.ExecDataTable(sql);          
            List<BlitemModel> list = new List<BlitemModel>();      
            foreach (DataRow item in table.Rows)
            {
                BlitemModel b = new BlitemModel();
                b.Blitem_Id = Convert.ToInt32(item["Blitem_Id"]);
                b.Blitem_Etc = item["Blitem_Etc"].ToString();
                b.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                b.Blitem_date = Convert.ToDateTime(item["Blitem_date"]);
                b.Blitem_num = Convert.ToInt32(item["Blitem_num"]);
                b.Blitem_Rownum = Convert.ToInt32(item["Blitem_Rownum"]);
                b.Blitem_Stock_num = Convert.ToInt32(item["Blitem_Stock_num"]);
                b.Product_Id = Convert.ToInt32(item["Product_Id"]);
                b.Blitem_Type_Id = Convert.ToInt32(item["Blitem_Type_Id"]);
                b.WareHose_Name = item["WareHose_Name"].ToString();
                b.Product_Name = item["Product_Name"].ToString();
                b.Blitem_Type_name = item["Blitem_Type_name"].ToString();
                list.Add(b);
            }
            return list;
        }
    }
}
